from tortoise import BaseDBAsyncClient


async def upgrade(db: BaseDBAsyncClient) -> str:
    return """
        CREATE TABLE IF NOT EXISTS `class` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(32) NOT NULL COMMENT '班级编号'
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `course` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(128) NOT NULL COMMENT '课程名'
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `student` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(32) NOT NULL COMMENT '姓名',
    `pwd` VARCHAR(32) NOT NULL COMMENT '密码',
    `sno` VARCHAR(10) NOT NULL UNIQUE COMMENT '学号',
    `avatar` VARCHAR(255) NOT NULL COMMENT '头像URL' DEFAULT '/static/image/默认头像.png',
    `email` VARCHAR(40) NOT NULL COMMENT '邮箱',
    `cla_id_id` INT NOT NULL,
    CONSTRAINT `fk_student_class_15c0ff53` FOREIGN KEY (`cla_id_id`) REFERENCES `class` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `grade` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `course_id_id` INT NOT NULL,
    `stu_id_id` INT NOT NULL,
    CONSTRAINT `fk_grade_course_62d2b62b` FOREIGN KEY (`course_id_id`) REFERENCES `course` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_grade_student_d78379af` FOREIGN KEY (`stu_id_id`) REFERENCES `student` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `teacher` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(32) NOT NULL COMMENT '姓名',
    `pwd` VARCHAR(32) NOT NULL COMMENT '密码',
    `num` VARCHAR(10) NOT NULL UNIQUE COMMENT '编号',
    `email` VARCHAR(40) NOT NULL COMMENT '邮箱',
    `avatar` VARCHAR(255) NOT NULL COMMENT '头像URL' DEFAULT '/static/image/默认头像.png',
    `course_id_id` INT NOT NULL,
    CONSTRAINT `fk_teacher_course_5345fb53` FOREIGN KEY (`course_id_id`) REFERENCES `course` (`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `vver_code` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `email` VARCHAR(50) NOT NULL UNIQUE COMMENT '收信人邮箱'
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `aerich` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `version` VARCHAR(255) NOT NULL,
    `app` VARCHAR(100) NOT NULL,
    `content` JSON NOT NULL
) CHARACTER SET utf8mb4;
CREATE TABLE IF NOT EXISTS `class_course` (
    `class_id` INT NOT NULL,
    `course_id` INT NOT NULL,
    FOREIGN KEY (`class_id`) REFERENCES `class` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`course_id`) REFERENCES `course` (`id`) ON DELETE CASCADE,
    UNIQUE KEY `uidx_class_cours_class_i_ad294f` (`class_id`, `course_id`)
) CHARACTER SET utf8mb4;"""


async def downgrade(db: BaseDBAsyncClient) -> str:
    return """
        """
